#!/bin/bash
# File Name: analysis_binlog.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-11 18:34:42
# Function: Analysis binlog for MySQL
#########################################################################
work_dir=/data/git/analysis_binlog
. ${work_dir}/function/logging.sh
mysqlbinlog="${1}"
binlog_file_path="${2}"
binlog_file="$(awk -F'/' '{print $NF}' <<< "${binlog_file_path}")"
sql_res_file="${3}"
save_type="${4}"
localhost_ip="${5}"
res_dir="$(awk -F'/' 'OFS="/",NF-=1' <<< "${sql_res_file}")/table"
[ ! -d "${res_dir}" ] && mkdir -p ${res_dir}
function f_analysis_binlog()
{
	eval ${mysqlbinlog} --base64-output=decode-rows -vv ${binlog_file_path} 2>/dev/null|awk -v res_dir="${res_dir}" -v binlog_file="${binlog_file}" -v save_type="${save_type}" 'BEGIN {
	    table=1;
		db_name="";
		t_name="";
		count=0;
		start=0;
		flag=0;
		s_count=0;
	}
	{
		if(match($0, /^#.*server id.*Table_map:.*mapped to number/) && flag==0) 
		{
			split($(NF-4),a,"`");
			db_name=a[2];
			t_name=a[4];
			t_name_tmp=(db_name"."t_name);
			flag=1;
			t_time=(substr($1,2,6)" "$2);
			res_file=(res_dir"/"binlog_file"_"t_name_tmp".log")
			if (table>1)
			{
				mark=0;
				for (table_tmp=1;table_tmp<=table;table_tmp++)
				{
					if (index(t_name_array[table_tmp],t_name_tmp))
					{
						mark=1;
						break;
					}
				}
				if(mark==0)
				{
					sql[t_name_tmp][0]=0;
					t_name_array[table]=t_name_tmp;
					table++;
					mark=0;
				}
			    else
				{
				   sql[t_name_tmp][0]=sql[t_name_tmp][0];
				}
			}
			else if(table==1)
			{
				sql[t_name_tmp][0]=0;
				t_name_array[1]=t_name_tmp;
				table++;
			}
			t_length=length(sql[t_name_tmp]);
			sql[t_name_tmp][t_length]=("BEGIN\n/*time:"t_time"*/");
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
		else if (match($0, /(### INSERT INTO .*\..*)/)) 
		{
			s_count+=1;
			t_length=length(sql[t_name_tmp]);
			#sql[t_name_tmp][t_length]=$0;
			sql[t_name_tmp][t_length]=("INSERT INTO "t_name_tmp);
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
		else if (match($0, /(### UPDATE .*\..*)/)) 
		{
			s_count+=1;
			t_length=length(sql[t_name_tmp]);
			#sql[t_name_tmp][t_length]=$0;
			sql[t_name_tmp][t_length]=("UPDATE "t_name_tmp);
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
		else if (match($0, /(### DELETE FROM .*\..*)/)) 
		{
			s_count+=1;
			t_length=length(sql[t_name_tmp]);
			sql[t_name_tmp][t_length]=("DELETE FROM "t_name_tmp);
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
		else if (match($0, /^(# at) /) && flag==1 && s_count>0) 
		{
			s_count=0;
		}
		else if (match($0, /^(COMMIT)/) && flag==1) 
		{
			flag=0;
			cont=0;
			t_length=length(sql[t_name_tmp]);
			sql[t_name_tmp][t_length]="COMMIT\n";
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
		else if (match($0, /^(COMMIT)/)) 
		{
			count=0;
			flag=0;
		}
	    else if (match($0, /(### )/))
		{
			t_length=length(sql[t_name_tmp]);
			split($0,tmp,"/*");
			split(tmp[1],tmp2,"###");
			sql[t_name_tmp][t_length]=tmp2[2];
			if (save_type=="table")
			{
			    printf "%s\n",sql[t_name_tmp][t_length] >> res_file;
			}
		}
	} END{
	    t_name_array_length=length(t_name_array);
		for (i=1;i<=t_name_array_length;i++)
		{
			sql_row=length(sql[t_name_array[i]]);
			for (j=1;j<=sql_row;j++)
			{
				tmp_var_table=t_name_array[i]
			    print sql[tmp_var_table][j];
			}
		}
    }' > ${sql_res_file}
	[ $? -eq 0 ] && f_logging "INFO" "Analysis completed --> ${binlog_file_path}" "2"|tee -a ${log_file}  || f_logging "ERROR" "Analysis completed --> ${binlog_file}"|tee -a ${log_file} 
}
f_analysis_binlog "${1}" "${2}" "${3}" "${4}"
